/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2011-6-11 14:17:55                           */
/*==============================================================*/
use master
go
if exists(select 1 from master..sysdatabases where name='dbCarLease')
	drop database dbCarLease
else
	create database dbCarLease
go
use dbCarLease
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ChuZuInfo') and o.name = 'FK_CHUZUINF_RELATIONS_CARINFO')
alter table ChuZuInfo
   drop constraint FK_CHUZUINF_RELATIONS_CARINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ChuZuInfo') and o.name = 'FK_CHUZUINF_RELATIONS_KEHUINFO')
alter table ChuZuInfo
   drop constraint FK_CHUZUINF_RELATIONS_KEHUINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TuiHuanInfo') and o.name = 'FK_TUIHUANI_RELATIONS_CHUZUINF')
alter table TuiHuanInfo
   drop constraint FK_TUIHUANI_RELATIONS_CHUZUINF
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TuiHuanInfo') and o.name = 'FK_TUIHUANI_RELATIONS_KEHUINFO')
alter table TuiHuanInfo
   drop constraint FK_TUIHUANI_RELATIONS_KEHUINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TuiHuanInfo') and o.name = 'FK_TUIHUANI_RELATIONS_CARINFO')
alter table TuiHuanInfo
   drop constraint FK_TUIHUANI_RELATIONS_CARINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('WeiZhangInfo') and o.name = 'FK_WEIZHANG_RELATIONS_CHUZUINF')
alter table WeiZhangInfo
   drop constraint FK_WEIZHANG_RELATIONS_CHUZUINF
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('WeiZhangInfo') and o.name = 'FK_WEIZHANG_RELATIONS_CARINFO')
alter table WeiZhangInfo
   drop constraint FK_WEIZHANG_RELATIONS_CARINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('WeiZhangInfo') and o.name = 'FK_WEIZHANG_RELATIONS_KEHUINFO')
alter table WeiZhangInfo
   drop constraint FK_WEIZHANG_RELATIONS_KEHUINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('XuZuInfo') and o.name = 'FK_XUZUINFO_RELATIONS_CHUZUINF')
alter table XuZuInfo
   drop constraint FK_XUZUINFO_RELATIONS_CHUZUINF
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('YuZuInfo') and o.name = 'FK_YUZUINFO_RELATIONS_CARINFO')
alter table YuZuInfo
   drop constraint FK_YUZUINFO_RELATIONS_CARINFO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('YuZuInfo') and o.name = 'FK_YUZUINFO_RELATIONS_KEHUINFO')
alter table YuZuInfo
   drop constraint FK_YUZUINFO_RELATIONS_KEHUINFO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CarInfo')
            and   type = 'U')
   drop table CarInfo
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ChuZuInfo')
            and   name  = 'Relationship_2_FK'
            and   indid > 0
            and   indid < 255)
   drop index ChuZuInfo.Relationship_2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ChuZuInfo')
            and   name  = 'Relationship_1_FK'
            and   indid > 0
            and   indid < 255)
   drop index ChuZuInfo.Relationship_1_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ChuZuInfo')
            and   type = 'U')
   drop table ChuZuInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('KeHuInfo')
            and   type = 'U')
   drop table KeHuInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Setting')
            and   type = 'U')
   drop table Setting
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TuiHuanInfo')
            and   name  = 'Relationship_5_FK'
            and   indid > 0
            and   indid < 255)
   drop index TuiHuanInfo.Relationship_5_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TuiHuanInfo')
            and   name  = 'Relationship_4_FK'
            and   indid > 0
            and   indid < 255)
   drop index TuiHuanInfo.Relationship_4_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TuiHuanInfo')
            and   name  = 'Relationship_3_FK'
            and   indid > 0
            and   indid < 255)
   drop index TuiHuanInfo.Relationship_3_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TuiHuanInfo')
            and   type = 'U')
   drop table TuiHuanInfo
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('WeiZhangInfo')
            and   name  = 'Relationship_8_FK'
            and   indid > 0
            and   indid < 255)
   drop index WeiZhangInfo.Relationship_8_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('WeiZhangInfo')
            and   name  = 'Relationship_7_FK'
            and   indid > 0
            and   indid < 255)
   drop index WeiZhangInfo.Relationship_7_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('WeiZhangInfo')
            and   name  = 'Relationship_6_FK'
            and   indid > 0
            and   indid < 255)
   drop index WeiZhangInfo.Relationship_6_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('WeiZhangInfo')
            and   type = 'U')
   drop table WeiZhangInfo
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('XuZuInfo')
            and   name  = 'Relationship_9_FK'
            and   indid > 0
            and   indid < 255)
   drop index XuZuInfo.Relationship_9_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('XuZuInfo')
            and   type = 'U')
   drop table XuZuInfo
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('YuZuInfo')
            and   name  = 'Relationship_11_FK'
            and   indid > 0
            and   indid < 255)
   drop index YuZuInfo.Relationship_11_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('YuZuInfo')
            and   name  = 'Relationship_10_FK'
            and   indid > 0
            and   indid < 255)
   drop index YuZuInfo.Relationship_10_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('YuZuInfo')
            and   type = 'U')
   drop table YuZuInfo
go

/*==============================================================*/
/* Table: CarInfo                                               */
/*==============================================================*/
create table CarInfo (
   iCarid               int              identity,
   sCType               varchar(30)          not null,
   sCPId                varchar(15)          not null,
   sCJId                varchar(30)          not null,
   sEngineId            varchar(30)          not null,
   mCPhoto              image                null,
   bBF                  bit                  null default 0,
   tBFDate              datetime             null,
   constraint PK_CARINFO primary key nonclustered (iCarid)
)
go

/*==============================================================*/
/* Table: ChuZuInfo                                             */
/*==============================================================*/
create table ChuZuInfo (
   iCZDId               int              identity,
   iKeHuId              int              not null,
   iCarid               int              not null,
   dRiFuJE              float                not null default 0,
   dYouHuiJE            float                null default 0,
   dYuFuJE              float                not null default 0,
   dYaJing              float                not null default 0,
   dShiFuJE             float                not null default 0,
   tStartDate           datetime             not null default(convert(varchar,getdate(),120)),
   tEndDate             datetime             not null,
   bXZ                  bit                  null default 0,
   bCZZF                bit                  null default 0,
   constraint PK_CHUZUINFO primary key nonclustered (iCZDId)
)
go

/*==============================================================*/
/* Index: Relationship_1_FK                                     */
/*==============================================================*/
create index Relationship_1_FK on ChuZuInfo (
iCarid ASC
)
go

/*==============================================================*/
/* Index: Relationship_2_FK                                     */
/*==============================================================*/
create index Relationship_2_FK on ChuZuInfo (
iKeHuId ASC
)
go

/*==============================================================*/
/* Table: KeHuInfo                                              */
/*==============================================================*/
create table KeHuInfo (
   iKeHuId              int              identity,
   sName                varchar(16)          not null,
   bSex                 bit                  not null default 1,
   sIdCard              varchar(18)          not null,
   sDriveId             varchar(18)          not null,
   bBlackList           bit                  null default 0,
   mPhoto               image                null,
   tRegiDate            datetime             not null default(convert(varchar,getdate(),120)),
   bLogout              bit                  null default 0,
   tLogoutDate          datetime             null,
   constraint PK_KEHUINFO primary key nonclustered (iKeHuId)
)
go

/*==============================================================*/
/* Table: Setting                                               */
/*==============================================================*/
create table Setting (
   id                   int              identity,
   pwd                  varchar(128)         null,
   pwdBackup            varchar(128)         null,
   bShowCQ              bit                  null default 0,
   bShowDQ              bit                  null default 0,
   bShowYD              bit                  null default 0,
   bShowDate            bit                  null default 0,
   bShowMsg             bit                  null default 0,
   bShowCaption         bit                  null default 0,
   bRembPwd             bit                  null default 0,
   bRegi                bit                  null default 0,
   caption              varchar(30)          null,
   dtRegidate           datetime             null default(convert(varchar,getdate(),120)),
   prodCode             varchar(50)          null,
   constraint PK_SETTING primary key (id)
)
go

/*==============================================================*/
/* Table: TuiHuanInfo                                           */
/*==============================================================*/
create table TuiHuanInfo (
   iTHDId               int              identity,
   iCarid               int              not null,
   iKeHuId              int              not null,
   iCZDId               int              null,
   tStartDate           datetime             not null,
   tEndDate             datetime             not null,
   tJSRQ                datetime             not null,
   iCZDays              int                  not null default 0,
   iXZDays              int                  null default 0,
   dXuZuSF		float		     null default 0,
   dYuFuJE              float                null default 0,
   dYaJing              float                null default 0,
   dYouHui              float                null default 0,
   dRiFu                float                not null default 0,
   dYingFu              float                not null default 0,
   dShiFu               float                not null default 0,
   dWZ                  float                null default 0,
   dYS                  float                not null default 0,
   dYT                  float                not null default 0,
   sMore                varchar(500)         null,
   imgKC                image                null,
   bTHZF                bit                  null default 0,
   constraint PK_TUIHUANINFO primary key nonclustered (iTHDId)
)
go

/*==============================================================*/
/* Index: Relationship_3_FK                                     */
/*==============================================================*/
create index Relationship_3_FK on TuiHuanInfo (
iCZDId ASC
)
go

/*==============================================================*/
/* Index: Relationship_4_FK                                     */
/*==============================================================*/
create index Relationship_4_FK on TuiHuanInfo (
iKeHuId ASC
)
go

/*==============================================================*/
/* Index: Relationship_5_FK                                     */
/*==============================================================*/
create index Relationship_5_FK on TuiHuanInfo (
iCarid ASC
)
go

/*==============================================================*/
/* Table: WeiZhangInfo                                          */
/*==============================================================*/
create table WeiZhangInfo (
   iWZDId               int              identity,
   iCarid               int              not null,
   iKeHuId              int              not null,
   iCZDId               int              not null,
   sWZAddr              varchar(30)          not null,
   sWZDetail            varchar(100)         not null,
   dWZJE                float                null default 0,
   tWZDate              datetime             null default(convert(varchar,getdate(),120)),
   bWZZF                bit                  null default 0,
   constraint PK_WEIZHANGINFO primary key nonclustered (iWZDId)
)
go

/*==============================================================*/
/* Index: Relationship_6_FK                                     */
/*==============================================================*/
create index Relationship_6_FK on WeiZhangInfo (
iCZDId ASC
)
go

/*==============================================================*/
/* Index: Relationship_7_FK                                     */
/*==============================================================*/
create index Relationship_7_FK on WeiZhangInfo (
iCarid ASC
)
go

/*==============================================================*/
/* Index: Relationship_8_FK                                     */
/*==============================================================*/
create index Relationship_8_FK on WeiZhangInfo (
iKeHuId ASC
)
go

/*==============================================================*/
/* Table: XuZuInfo                                              */
/*==============================================================*/
create table XuZuInfo (
   iXZDId               int              identity,
   iCZDId               int              not null,
   dXZJE                float                not null,
   tOldDate             datetime             not null,
   tNewDate             datetime             not null,
   bXZZF                bit                  null default 0,
   constraint PK_XUZUINFO primary key nonclustered (iXZDId)
)
go

/*==============================================================*/
/* Index: Relationship_9_FK                                     */
/*==============================================================*/
create index Relationship_9_FK on XuZuInfo (
iCZDId ASC
)
go

/*==============================================================*/
/* Table: YuZuInfo                                              */
/*==============================================================*/
create table YuZuInfo (
   iYZDId               int              identity,
   iKeHuId              int              not null,
   iCarid               int              not null,
   dYuFuJE              float                null default 0,
   tYZDate              datetime             not null default(convert(varchar,getdate(),120)),
   bYZZF                bit                  null default 0,
   constraint PK_YUZUINFO primary key nonclustered (iYZDId)
)
go

/*==============================================================*/
/* Index: Relationship_10_FK                                    */
/*==============================================================*/
create index Relationship_10_FK on YuZuInfo (
iCarid ASC
)
go

/*==============================================================*/
/* Index: Relationship_11_FK                                    */
/*==============================================================*/
create index Relationship_11_FK on YuZuInfo (
iKeHuId ASC
)
go

alter table ChuZuInfo
   add constraint FK_CHUZUINF_RELATIONS_CARINFO foreign key (iCarid)
      references CarInfo (iCarid)
go

alter table ChuZuInfo
   add constraint FK_CHUZUINF_RELATIONS_KEHUINFO foreign key (iKeHuId)
      references KeHuInfo (iKeHuId)
go

alter table TuiHuanInfo
   add constraint FK_TUIHUANI_RELATIONS_CHUZUINF foreign key (iCZDId)
      references ChuZuInfo (iCZDId)
go

alter table TuiHuanInfo
   add constraint FK_TUIHUANI_RELATIONS_KEHUINFO foreign key (iKeHuId)
      references KeHuInfo (iKeHuId)
go

alter table TuiHuanInfo
   add constraint FK_TUIHUANI_RELATIONS_CARINFO foreign key (iCarid)
      references CarInfo (iCarid)
go

alter table WeiZhangInfo
   add constraint FK_WEIZHANG_RELATIONS_CHUZUINF foreign key (iCZDId)
      references ChuZuInfo (iCZDId)
go

alter table WeiZhangInfo
   add constraint FK_WEIZHANG_RELATIONS_CARINFO foreign key (iCarid)
      references CarInfo (iCarid)
go

alter table WeiZhangInfo
   add constraint FK_WEIZHANG_RELATIONS_KEHUINFO foreign key (iKeHuId)
      references KeHuInfo (iKeHuId)
go

alter table XuZuInfo
   add constraint FK_XUZUINFO_RELATIONS_CHUZUINF foreign key (iCZDId)
      references ChuZuInfo (iCZDId)
go

alter table YuZuInfo
   add constraint FK_YUZUINFO_RELATIONS_CARINFO foreign key (iCarid)
      references CarInfo (iCarid)
go

alter table YuZuInfo
   add constraint FK_YUZUINFO_RELATIONS_KEHUINFO foreign key (iKeHuId)
      references KeHuInfo (iKeHuId)
go

